package com.geneqiao.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.geneqiao.jdbc.build.ISqlTranction;
import com.geneqiao.jdbc.build.PageInfo;
import com.geneqiao.jdbc.jpa.ProcdureParamater;
import com.geneqiao.jdbc.jpa.ReflectJPA;
import com.geneqiao.jdbc.jpa.TableStruct;
import com.geneqiao.jdbc.pool.JDBCDataSource;
import com.geneqiao.jdbc.pool.JDBCMap;
import com.geneqiao.jdbc.pool.SelectConn;
import com.geneqiao.jdbc.util.ObjectUtils;
import com.geneqiao.jdbc.util.ParameterType;

public abstract class AbstractSession
{
	private static final Logger logger = Logger.getLogger(AbstractSession.class);

	protected JDBCDataSource jdbcDataSource = null;
	protected static final String NULL = "null";
	private static SelectConn selectConn;
	private static final Object obj2 = new Object();

	// 获取带事务的连接
	public ISqlTranction beginTranction() throws SQLException
	{
		Connection conn = jdbcDataSource.getConnection();
		conn.setAutoCommit(false);
		return new DefaultTranctionImpl(conn);
	}

	protected synchronized Connection getSelectConnection()
	{
		if (selectConn == null)
			selectConn = jdbcDataSource.getSelectConnection();
		return selectConn.getConnection();
	}

	public <T> List<T> Query(ISqlTranction tran, Class<T> t) throws SQLException
	{
		TableStruct struct = getEntity(t);
		String sql = "select * from " + struct.getTableName();
		return Query(tran, t, sql, NULL);
	}

	public <T> List<T> Query(ISqlTranction tran, Class<T> t, String sql) throws SQLException
	{
		return Query(tran, t, sql, NULL);
	}

	public <T> List<T> Query(ISqlTranction tran, Class<T> t, String sql, Object... objects) throws SQLException
	{
		return buildQuery(t, sql, tran.Connection(), objects);
	}

	public <T> List<T> Query(Class<T> t) throws SQLException
	{
		TableStruct struct = getEntity(t);
		return Query(t, "select * from " + struct.getTableName(), NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @return Model Collection
	 */
	public <T> List<T> Query(Class<T> t, String sql)
	{
		return Query(t, sql, NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @param autoMap
	 *            是否字段转换列名进行映射
	 * @return 查询的对象集合
	 */
	public <T> List<T> Query(Class<T> t, String sql, Object... objects)
	{
		try
		{
			return buildQuery(t, sql, getSelectConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("Get result faild => " + sql, e);
			return null;
		}
	}

	public <T> PageInfo queryByPage(Class<T> t, int pageNum, int pageSize, String sql)
	{
		return queryByPage(t, pageNum, pageSize, sql, NULL);
	}

	public <T> PageInfo queryByPage(Class<T> t, int pageNum, int pageSize, String sql, Object... objects)
	{
		PageInfo page = new PageInfo(pageNum, pageSize);
		if (ObjectUtils.isEmpty(objects))
		{
			page.setResult(Query(t, sql + " LIMIT ?,?", page.getStartRow(), pageSize));
			page.setTotal(Long.parseLong(getSingle("select count(1) from (" + sql + ") a").toString()));
		}
		else
		{
			Object[] newObj = new Object[objects.length + 2];
			System.arraycopy(objects, 0, newObj, 0, objects.length);
			// 赋值LIMIT参数
			newObj[objects.length] = page.getStartRow();
			newObj[objects.length + 1] = pageSize;
			page.setResult(Query(t, sql + " LIMIT ?,?", newObj));
			page.setTotal(Long.parseLong(getSingle("select count(1) from (" + sql + ") a", objects).toString()));
		}
		return page;
	}

	protected abstract <T> List<T> buildQuery(Class<T> t, String sql, Connection conn, Object... objects)
			throws SQLException;

	public <T> List<T> QueryList(ISqlTranction tran, String sql) throws SQLException
	{
		return QueryList(tran, sql, NULL);
	}

	public <T> List<T> QueryList(ISqlTranction tran, String sql, Object... objects) throws SQLException
	{
		return buildQueryList(sql, tran.Connection(), objects);
	}

	/***
	 * 
	 * @param sql
	 *            查询的SQL
	 * @return 查询的对象集合
	 */
	public <T> List<T> QueryList(String sql)
	{
		return QueryList(sql, NULL);
	}

	/**
	 * 
	 * @param sql
	 * @param objects
	 *            parameters
	 * @return 查询的单列集合
	 */
	public <T> List<T> QueryList(String sql, Object... objects)
	{
		try
		{
			return buildQueryList(sql, getSelectConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("获取查询结果失败 =>" + sql, e);
			return null;
		}
	}

	protected abstract <T> List<T> buildQueryList(String sql, Connection conn, Object... objects) throws SQLException;

	/***
	 * 执行存储过程
	 * 
	 * @param sql
	 *            存储过程名
	 * @return 执行结果
	 */
	public boolean RunProcdure(String sql)
	{
		return RunProcdure(sql, null);
	}

	/***
	 * 执行存储过程
	 * 
	 * @param sql
	 *            存储过程名
	 * @param paras
	 *            存储过程参数
	 * @return 执行结果
	 */
	public boolean RunProcdure(String sql, List<ProcdureParamater> paras)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			CallableStatement cs = ObjectUtils.buildStatement(conn, sql);
			if (paras != null)
			{
				for (ProcdureParamater para : paras)
				{
					switch (para.getParameterType())
					{
					case IN:
						cs.setObject(para.getSort(), para.getValue());
						break;
					case OUT:
						cs.registerOutParameter(para.getSort(), Types.JAVA_OBJECT);
						break;
					default:
						cs.setObject(para.getSort(), para.getValue());
						cs.registerOutParameter(para.getSort(), Types.JAVA_OBJECT);
						break;
					}
				}
				// 执行存储过程
				cs.execute();
				for (ProcdureParamater para : paras)
				{
					if (para.getParameterType() == ParameterType.OUT || para.getParameterType() == ParameterType.INOUT)
					{
						para.setValue(cs.getObject(para.getSort()));
					}
				}
			}
			else
			{
				cs.execute();
			}
			cs.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("Procdure => " + sql);
				logger.debug("Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Run procdure faild =>" + sql, e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public <T> T Single(ISqlTranction tran, Class<T> t, String sql) throws SQLException
	{
		return Single(tran, t, sql, NULL);
	}

	public <T> T Single(ISqlTranction tran, Class<T> t, String sql, Object... objects) throws SQLException
	{
		List<T> dataList = Query(tran, t, sql, objects);
		if (dataList != null)
		{
			if (dataList.size() == 1)
				return dataList.get(0);
			if (dataList.size() > 1)
				throw new SQLException("Too many results were found :" + dataList.size());
		}
		return null;
	}

	public <T> T Single(Class<T> t, String sql)
	{
		return Single(t, sql, NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @param autoMap
	 *            是否字段转换列名进行映射
	 * @return 返回单个对象
	 * 
	 */
	public <T> T Single(Class<T> t, String sql, Object... objects)
	{
		List<T> dataList = Query(t, sql, objects);
		if (dataList != null && dataList.size() == 1) { return dataList.get(0); }
		return null;
	}

	public Map<String, Object> QueryMap(ISqlTranction tran, String sql) throws SQLException
	{
		return QueryMap(tran, sql, NULL);
	}

	public Map<String, Object> QueryMap(ISqlTranction tran, String sql, Object... objects) throws SQLException
	{
		return buildQueryMap(sql, tran.Connection(), objects);
	}

	public Map<String, Object> QueryMap(String sql)
	{
		return QueryMap(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return 查询的键值对
	 */
	public Map<String, Object> QueryMap(String sql, Object... objects)
	{
		try
		{
			return buildQueryMap(sql, getSelectConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("Get map<k,v> faild =>" + sql, e);
			return null;
		}
	}

	protected abstract Map<String, Object> buildQueryMap(String sql, Connection conn, Object... objects)
			throws SQLException;

	public Object getSingle(ISqlTranction tran, String sql) throws SQLException
	{
		return getSingle(tran, sql, NULL);
	}

	public Object getSingle(ISqlTranction tran, String sql, Object... objects) throws SQLException
	{
		return buildGetSingle(sql, tran.Connection(), objects);
	}

	public Object getSingle(String sql)
	{
		return getSingle(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return 返回单个值
	 */
	public Object getSingle(String sql, Object... objects)
	{
		try
		{
			return buildGetSingle(sql, getSelectConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("Get single faild =>" + sql, e);
			return null;
		}
	}

	protected abstract Object buildGetSingle(String sql, Connection conn, Object... objects) throws SQLException;

	public boolean Execute(ISqlTranction tran, String sql) throws SQLException
	{
		return Execute(tran, sql, NULL);
	}

	public boolean Execute(ISqlTranction tran, String sql, Object... objects) throws SQLException
	{
		return buildExecute(sql, tran.Connection(), objects);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return boolean
	 */
	public boolean Execute(String sql)
	{
		return Execute(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @param objects
	 *            执行sql语句对应的参数
	 * @return boolean
	 */
	public boolean Execute(String sql, Object... objects)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildExecute(sql, conn, objects);
		}
		catch (SQLException e)
		{
			logger.error("Execute SQL faild => " + sql, e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	protected abstract boolean buildExecute(String sql, Connection conn, Object... objects) throws SQLException;

	public boolean Execute(List<String> sqlList)
	{
		long start = getNow();
		ISqlTranction tran = null;
		try
		{
			tran = beginTranction();
			for (String sql : sqlList)
			{
				Execute(tran, sql);
			}
			tran.commit();
			logger.debug("Executed, Nums => " + sqlList.size() + ", Times => " + (getNow() - start) + "ns");
			return true;
		}
		catch (SQLException e)
		{
			tran.rollback();
			logger.error("Execute sqlList faild", e);
			return false;
		}
		finally
		{
			close(tran);
		}
	}

	public boolean save(ISqlTranction tran, Object obj) throws SQLException
	{
		if (obj instanceof List)
			throw new SQLException("不支持List对象的事务处理");
		TableStruct struct = getEntity(obj.getClass());
		return buildSave(tran.Connection(), struct, obj);
	}

	protected abstract boolean buildSave(Connection conn, TableStruct struct, Object obj) throws SQLException;

	public boolean save(Object obj)
	{
		if (obj instanceof List)
			return saveList((List<?>) obj);

		List<Object> objs = new ArrayList<>();
		objs.add(obj);
		return saveList(objs);
	}

	protected abstract boolean saveList(List<?> objs);

	public boolean update(Object obj)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildUpdate(conn, obj, false);
		}
		catch (SQLException e)
		{
			logger.error("Update object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public boolean update(ISqlTranction tran, Object obj) throws SQLException
	{
		return buildUpdate(tran.Connection(), obj, false);
	}

	public boolean updateNotNull(Object obj)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildUpdate(conn, obj, true);
		}
		catch (SQLException e)
		{
			logger.error("Update object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public boolean updateNotNull(ISqlTranction tran, Object obj) throws SQLException
	{
		return buildUpdate(tran.Connection(), obj, true);
	}

	protected abstract boolean buildUpdate(Connection conn, Object obj, boolean notNull) throws SQLException;

	public boolean delete(Object obj)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildDelete(conn, obj);
		}
		catch (SQLException e)
		{
			logger.error("Delete object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public boolean delete(ISqlTranction tran, Object obj) throws SQLException
	{
		return buildDelete(tran.Connection(), obj);
	}

	protected abstract boolean buildDelete(Connection conn, Object obj) throws SQLException;

	public boolean saveOrUpdate(Object obj)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildSaveOrUpdate(conn, obj);
		}
		catch (SQLException e)
		{
			logger.error("Delete object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public boolean saveOrUpdate(ISqlTranction tran, Object obj) throws SQLException
	{
		return buildSaveOrUpdate(tran.Connection(), obj);
	}

	protected abstract boolean buildSaveOrUpdate(Connection conn, Object obj) throws SQLException;

	protected TableStruct getEntity(Class<?> cla) throws SQLException
	{
		synchronized (obj2)
		{
			if (!JDBCMap.findEntity(cla))
			{
				TableStruct struct = ReflectJPA.loadTable(cla);
				if (struct == null)
					throw new SQLException("No annotations for '" + cla.getName() + "' were found");
				JDBCMap.addEntity(cla.getName(), struct);
			}
			return JDBCMap.getEntity(cla);
		}
	}

	public void close(ISqlTranction tran)
	{
		jdbcDataSource.releaseConnection(tran.Connection(), true);
	}

	protected static long getNow()
	{
		return System.nanoTime();
	}
}
